const pool = require('./db');
const moment = require('moment');
const queryAllArticlesDB = () => {
    return new Promise((resolve, reject) => {
        pool.getConnection(function (err, connection) {
            if (err) throw err; // 错误
            connection.query('SELECT * FROM article order by time desc', function (error, results, fields) {
                // 首先释放资源
                connection.release();
                // 处理错误
                if (error) {
                    reject(error);
                } else {
                    resolve(results);
                }
            });
        })
    });
}

const queryArticleOnIDDB = (articleID) => {
    return new Promise((resolve, reject) => {
        pool.getConnection(function (err, connection) {
            if (err) throw err; // 错误
            connection.query(`UPDATE article SET views = views+1 where articleID= ${connection.escape(articleID)}`,
                function (error, results, fields) {
                    // 处理错误
                    if (error) {
                        reject(error);
                    }
                });
            connection.query(`SELECT * FROM article where articleID = ${connection.escape(articleID)}`,
                function (error, results, fields) {
                    // 首先释放资源
                    connection.release();
                    // 处理错误
                    if (error) {
                        reject(error);
                    } else {
                        resolve(results[0]);
                    }
                });
        })
    });
}

const queryArticlesOnCateDB = (cateID) => {
    return new Promise((resolve, reject) => {
        pool.getConnection(function (err, connection) {
            if (err) throw err; // 错误
            connection.query(`SELECT * FROM article where cateID = ${connection.escape(cateID)} order by time desc`, function (error, results, fields) {
                // 首先释放资源
                connection.release();
                // 处理错误
                if (error) {
                    reject(error);
                } else {
                    resolve(results);
                }
            });
        })
    });
}

const addArticleDB = (title, markdown, cateID) => {
    return new Promise((resolve, reject) => {
        pool.getConnection(function (err, connection) {
            if (err) throw err; // 错误
            connection.query(
                `INSERT INTO article ( title, time, content, cateID ) VALUES`
                + `( ${connection.escape(title)},${connection.escape(moment().format('YYYY-MM-DD HH:mm:ss'))}`
                + `,${connection.escape(markdown)},${connection.escape(cateID)} );`
                , function (error, results, fields) {
                    // 首先释放资源
                    connection.release();
                    // 处理错误
                    if (error) {
                        reject(error);
                    } else {
                        resolve(results);
                    }
                });
        })
    });
}

// 编辑文章
const editArticleDB = (articleID, title, markdown, cateID) => {
    return new Promise((resolve, reject) => {
        pool.getConnection(function (err, connection) {
            if (err) throw err; // 错误
            connection.query(
                `UPDATE article set title = ${connection.escape(title)},` +
                `content = ${connection.escape(markdown)},` +
                `cateID = ${connection.escape(cateID)} ` +
                `WHERE articleID=${connection.escape(articleID)}`
                , function (error, results) {
                    // 首先释放资源
                    connection.release();
                    // 处理错误
                    if (error) {
                        reject(error);
                    } else {
                        resolve(results);
                    }
                });
        })
    });
}

// 删除文章
const deleteArticleDB = (articleID) => {
    return new Promise((resolve, reject) => {
        pool.getConnection(function (err, connection) {
            if (err) throw err; // 错误
            connection.query(
                `DELETE FROM article WHERE articleID=${connection.escape(articleID)}`
                , function (error, results) {
                    // 首先释放资源
                    connection.release();
                    // 处理错误
                    if (error) {
                        reject(error);
                    } else {
                        resolve(results);
                    }
                });
        })
    });
}

exports.queryAllArticlesDB = queryAllArticlesDB;
exports.queryArticleOnIDDB = queryArticleOnIDDB;
exports.queryArticlesOnCateDB = queryArticlesOnCateDB;
exports.addArticleDB = addArticleDB;
exports.editArticleDB = editArticleDB;
exports.deleteArticleDB = deleteArticleDB;